Allan B. Wilson; abwilson@awasu.com
Version 1.4; 07-Jun-05
This release adds support for a new hook parameter “dbTable”, which is the name of the MySQL database table to use for the associated channel. This means each channel can have its own table in which to save news items—or various channels can (still) share a single table. If unspecified, dbTable defaults to “NewsItems”, so all such channels will have their data placed in this one table. Note that each database table specified must exist (and be defined as shown below for NewsItems) so the hook can use it!
usawa.exe has also been modified to accept the name of the table to use for its queries; its default is also “NewsItems”, providing its previous behavior.
Finally, the channel name (the one displayed in Control Center) is added as a new field in each database entry. If you have modified the name in Awasu’s channel creation/modification dialog, that name will be used; otherwise the name in the feed is used.
If you want to use data saved with an earlier version of this hook with this updated release you’ll need to add a ChannelName field to your existing data as shown in the table creation example below.
This Channel Hook is invoked by Awasu when it detects the arrival of at least one new item in the associated channel feed; the hook saves all new items in a MySQL database. This means you can easily keep a history of selected news items in a powerful, freely available relational database that supports FULLTEXT searching.
There is also a companion command-line utility, usawa.exe, to search and view the database in HTML (in a browser). It provides a quick and easy interface to the stored news-item data, which otherwise would require manual SQL commands to view. Of course, once you have installed MySQL, its full power is available, so using the utility is optional—if you prefer using SQL, go for it!
There is no charge for personal or non-profit use. For commercial use or any required enhancements, please e-mail the author.
The MySQL database package must be installed before using this channel hook or the accompanying utility. If you have already installed MySQL, you can skip this section; however, you will still need to create a MySQL user account and password as well as the hook database and table; these steps are detailed in the next section.
To install MySQL, go to the download page at MySQL downloads and retrieve the Windows installation package (for example, “Windows Essentials (x86)”) for MySQL version 4.1 or a later 4.x release (when these notes were written, the starting links were listed under the heading “MySQL database server & standard clients”). Read the appropriate documentation to get MySQL installed and running.
mysqld-nt.exe running as a Windows XP service seems to work well. This is the full-featured version of the database for Windows; by running it as a service, MySQL is automatically available as soon as the system boots.
Once MySQL is successfully installed and running, you need to prepare it so the hook can use it. The hook has been created to use a default database (with one specific table) and run with a default user account/password; all of these except the details of the specific table can be changed if you wish. Of course you can simply use the “built-in” values and therefore you should use the exact commands below.
First, you need to create a database. Running as a MySQL administrator using the mysql database, execute the command:
CREATE DATABASE AwasuNewsItemDB;
Next, create a user account, with full privileges to the new database (account name awasu, password awasu), like this:
GRANT ALL PRIVILEGES ON AwasuNewsItemDB.* TO awasu@localhost IDENTIFIED BY ‘awasu’;
GRANT ALL PRIVILEGES ON AwasuNewsItemDB.* TO awasu IDENTIFIED BY ‘awasu’;
Finally, create the single default database table (and any optional ones) the hook uses by running createNewsItemTable.exe [table_name]; the default, as shown below, is “NewsItems”. This will execute the command:
CREATE TABLE AwasuNewsItemDB.NewsItems ( ItemId INTEGER NOT NULL, PRIMARY KEY (ItemId), TimeCreated DATETIME NOT NULL, INDEX timecreatedindex (TimeCreated), ChannelName VARCHAR(128) NULL, ItemTitle TEXT NULL, ItemUrl TEXT NULL, ItemMetadata TEXT NULL, PlainTextDescription MEDIUMTEXT NULL, MarkedUpDescription MEDIUMTEXT NULL, FULLTEXT fulltextindex (ChannelName, ItemTitle, ItemUrl, ItemMetadata, PlainTextDescription) ) TYPE=MyISAM CHARACTER SET utf8;
Note that if you ever want to get rid of all previous news-item information in a database table, you can execute the following command:
DROP TABLE IF EXISTS AwasuNewsItemDB.NewsItems;
(Substitute the name of the table to delete in place of the default [“NewsItems”] above.)
If the table exists, the command above—without warning—destroys it and all its contents. This will wipe out any news-item data previously stored!
This is a standard Awasu channel hook. Install it by viewing Properties for an existing channel (by right-clicking on a channel in the Control Center and selecting Properties) or, in newer version of Awasu, you can set an advanced option to display the hook dialog when a channel is created. If you are viewing the properties of an existing channel, once the channel properties window is displayed, click on the “Hooks” icon. This will open a new window that lets you specify the hook. If instead you have set the Awasu option to display the Channel Hook dialog when creating a new channel, wait for it in the sequence of Awasu dialogs. Once the Channel Hook dialog box is open, click on the create-hook icon, which is the one to the left of the “X” icon (which “disconnects” a channel hook); then navigate to and select the channel hook.
Once selected, you’ll need to supply values to identify the database host system, MySQL user account and password, database to use for logging the associated RSS feed, and optional database table and logfile name. If the MySQL server is running on the system where Awasu is running, use “localhost” (or “127.0.0.1”) as the server name. Of course the user account, password, database name, and table name should match the information you specified when you prepared MySQL for the hook, as described in the section immediately above.
You can run this channel hook on multiple channels to save multiple channel feed items simultaneously. You may store all the items in a single database and table or use a dedicated database and/or table for one or more channels.
Note that you must install MySQL before associating this hook with any Awasu channel. See the sections above for information about MySQL installation and configuration, and preparing it for use.
If you have problems getting the hook to run, turn on Awasu’s hook logging in the hook properties dialog (reached from its associated channel’s properties dialog, as described under Basic Operation). Then you can see any error output from the hook when Awasu invokes it. You can also enable the hook’s own logging facility by entering a logfile name in the hook creation dialog; examine the logfile to follow the hook’s execution progress.
Awasu invokes the hook each time it detects at least one new item in the associated channel. Once invoked, the hook saves a copy of each new item in the MySQL database and exits.
This plug-in was written in Python and the resulting code “frozen” to create the standalone executable file. The code is frozen with the py2exe tool.
usawa.exe: A command-line utility to search the MySQL database for items and display matches in the default browserAs mentioned earlier, once you have installed MySQL on your system you have its full power available. You can use SQL to query the database, generate reports, show statistics, etc. However, if all you want to do is search the database for news items meeting certain conditions, you should give usawa.exe a try.
To use usawa.exe, open a command window (for example, by selecting Start/Run…, entering “cmd” in the Open box, and clicking OK). Once the command window is open, to see the program’s usage information and options enter “usawa.exe -h” (without the quotation marks, but—not shown—specifying the directory in which the program resides, if necessary) and press Enter.
A search can be as simple as “usawa.exe citation” to search for news items containing the text “citation”. You can search for multiple items simultaneously: To search for news items about Python or Perl, enter “usawa.exe python perl”.
If any news items match your search criteria, usawa.exe will display them in your default browser.
Please refer to the MySQL FULLTEXT documentation for complete searching details. However, in a nutshell, include words you want to find in the stored RSS feeds; feeds containing any of the words will match. Including any of the special characters below turns your search into a BOOLEAN search; again, refer to the documentation.
+ A leading plus sign indicates that this word must be present in a feed. - A leading minus sign indicates that this word must not be present in a feed. < > The < operator decreases a word's contribution to the match; the > operator increases it. ( ) Parentheses are used to group words into subexpressions. ~ A leading tilde acts as a negation operator, causing the word's contribution to the feed relevance to be negative. * An asterisk is the truncation operator. Unlike the other operators, it should be appended to the word, not prepended. " A phrase enclosed in double quotes matches only feeds that contain this literal phrase.
Here are some examples:
apple banana
find feeds that contain at least one of these words.
+apple +juice
... both words.
+apple macintosh
... word “apple”, but rank it higher if it also contains “macintosh”.
+apple -macintosh
... word “apple” but not “macintosh”.
“some words”
... “some words of wisdom”, but not “some noise words”.